package com.zr.dao.impl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.dao.UserDao;
import com.zr.model.User;
import com.zr.model.UserRole;
import com.zr.util.JDBCUtil;

/**
 * @Description
 * @author 郑少杰
 * @date 2017年9月13日 下午2:28:32
 * @version V1.3.1
 */

public class UserDaoImpl implements UserDao {
    /**
     * @Description 根据用户名和密码进行登录
     * @author 郑少杰
     * @param userName 用户名
     * @param userPassWd 密码
     * @return 登录成功后返回user对象
     */
    public User login(String userName, String userPassWd) {
        User user = new User();
        StringBuffer sql = new StringBuffer();
        sql.append("select * ");
        sql.append("from user ");
        sql.append("where username=? and userpasswd=?");
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql.toString());
            preparedStatement.setString(1, userName);
            preparedStatement.setString(2, userPassWd);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                user.setUserId(resultSet.getInt("userId"));
                user.setUserName(resultSet.getString("username"));
                user.setRoleId(resultSet.getInt("roleid"));
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCUtil.closeJDBC(preparedStatement, connection);
        }

        return user;

    }

    /**
     * Description
     * 
     * @param userName
     * @param userPassWd
     * @return
     * @see com.zr.dao.UserDao#addUser(java.lang.String, java.lang.String)
     */

    @Override
    public boolean addUser(String userName, String userPassWd, int roleId) {
        StringBuffer sql = new StringBuffer();
        sql.append("insert into user (username,userpasswd,roleId) ");
        sql.append("values(?,?,?)");
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql.toString());
            preparedStatement.setString(1, userName);
            preparedStatement.setString(2, userPassWd);
            preparedStatement.setInt(3, roleId);
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                return true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCUtil.closeJDBC(preparedStatement, connection);
        }
        return false;
    }

    /**
     * Description
     * 
     * @param userId
     * @return
     * @see com.zr.dao.UserDao#deleteUserById(int)
     */

    @Override
    public boolean deleteUserById(int userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("delete from user ");
        sql.append("where userid=?");
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql.toString());
            preparedStatement.setInt(1, userId);
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                return true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCUtil.closeJDBC(preparedStatement, connection);
        }
        return false;
    }

    /**
     * Description
     * 
     * @param page
     * @param pageSize
     * @return
     * @see com.zr.dao.UserDao#getUserRoles(int, int)
     */

    @Override
    public List<UserRole> getUserRoles(int page, int pageSize) {
        List<UserRole> list = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        sql.append("select user.userid,username,rolename ");
        sql.append("from user,role ");
        sql.append("where user.roleid=role.roleid ");
        sql.append("limit ?,?");
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql.toString());
            preparedStatement.setInt(1, (page - 1) * pageSize);
            preparedStatement.setInt(2, pageSize);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                UserRole userRole = new UserRole(resultSet.getInt("userid"),
                        resultSet.getString("username"), resultSet.getString("rolename"));
                list.add(userRole);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCUtil.closeJDBC(preparedStatement, connection);
        }
        return list;
    }

    /**
     * Description
     * 
     * @return
     * @see com.zr.dao.UserDao#getAllUserNum()
     */

    @Override
    public int getAllUserNum() {
        int num = 0;
        StringBuffer sql = new StringBuffer();
        sql.append("select count(*) as num ");
        sql.append("from user,role ");
        sql.append("where user.roleid=role.roleid ");

        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql.toString());

            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                num = resultSet.getInt("num");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCUtil.closeJDBC(preparedStatement, connection);
        }
        return num;
    }


}
